/** JDBC 2.0:
   JDBC 1.1 supports read-only and forward-only cursor/result set. JDBC 2.0 support two 
new types of result sets. Both allow users to move cursor back and forth within the
record set and allow users to insert, delete and update the current record. The difference
between two new types is that one allow you to read records updated by others and one doesn't.

   Each type of cursor can be created through the Connection function:

		 Statement createStatement(int resultSetType, int resultSetConcurrency)

where two parameters are ResultSet constants indicating whether the ResultSet is sensitive
or not and whether the concurrent update is allowed.
 
*/

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.sql.*;

public class JDBC2UpdatableRS extends CloseableJFrame implements ActionListener {

	Container c = null;
	JPanel	pnSouth = new JPanel(), pnCenter = new JPanel();
	String  strEmp[] = { "Emp#", "EName", "Job", "Manager", "Hired Date", "Salary", "Commission", "Dept #" };
	JTextField tfEmp[] = new JTextField[8];

	String strOP[] = { "First", "Prev.", "Next", "Last", "Delete", "Update", "Add" };
	JButton btnOP[] = new JButton[7];

	Connection cnn = null;
	Statement  stmt = null;
	ResultSet  rs = null;

	JDBC2UpdatableRS() {

		c = getContentPane();
		c.setLayout( new BorderLayout() );
		pnCenter.setLayout( new GridLayout(4, 4, 10, 5) );
		pnSouth.setLayout( new GridLayout(1, 7) );
		c.add(pnSouth, BorderLayout.SOUTH);
		c.add(pnCenter, BorderLayout.CENTER);
		for ( int i = 0; i < 8; i ++ ) {
			pnCenter.add( new JLabel(strEmp[i], SwingConstants.RIGHT) );
			tfEmp[i] = new JTextField(25);
			tfEmp[i].addActionListener(this);
			pnCenter.add( tfEmp[i] );
			if ( i == 4 ) tfEmp[i].setToolTipText("Enter date as 1999-12-20");
		}
		for ( int i = 0; i < strOP.length; i ++ ) {
			btnOP[i] = new JButton(strOP[i] ); btnOP[i].setSize(70, 20);
			btnOP[i].addActionListener( this );
			pnSouth.add( btnOP[i] ) ;
		}

		String	url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
			user="scott", passwd="tiger";

             	try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			cnn = DriverManager.getConnection(url, user, passwd);
			cnn.setAutoCommit(false); 
			stmt = cnn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			// If SELECT * FROM emp is used, the resultset become read-only.
			rs = stmt.executeQuery("SELECT EmpNo, EName, Job, Mgr, HireDate, Sal, Comm, DeptNo FROM emp");
			rs.first();
			fillTextFields();
	   	} catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }


		setTitle("JDBC 2.0 -- Scrollable and Updatable Cursor");
		setSize(580, 180); show();
	}


	public void actionPerformed( ActionEvent e) {
		Object obj = e.getSource();
		int cmdIndex = -1, empFDIndex = -1;

		for ( int i = 0; i < strOP.length; i++) 
			if ( obj.equals(btnOP[i])) { cmdIndex = i; break; }
	
		for ( int i = 0; i < tfEmp.length; i++) 
			if ( obj.equals(tfEmp[i])) { empFDIndex = i; break; }

		if ( empFDIndex > 0 ) { updateEmployeeField(empFDIndex); return; }

		try {
			switch ( cmdIndex ) {
				case 0: rs.first(); break; 				// move to first row.	
				case 1: if ( ! rs.isFirst() ) rs.previous(); break;
				case 2: if ( ! rs.isLast() ) rs.next(); break;
				case 3: rs.last(); break;
				case 4: deleteEmployee(); break;
				case 5: updateEmployeeFields(); break;
				case 6: if ( btnOP[6].getText().equals("Add") ) {
						clearEmployeeFields(); btnOP[6].setText("Save");
						JOptionPane.showMessageDialog(this, "Fill the data and click [Save].");
						break;
					} else { saveEmployee(); btnOP[6].setText("Add"); break; }
			}
		} catch (SQLException e1) { e1.printStackTrace(); }
		if ( cmdIndex >=0 && cmdIndex <= 5 ) fillTextFields();
	}	

	void clearEmployeeFields() {
		for ( int i = 0; i < tfEmp.length; i ++ ) tfEmp[i].setText("");
	}

	void saveEmployee() {
	   try {
		rs.moveToInsertRow();
		rs.updateInt(1, Integer.parseInt(tfEmp[0].getText())); // Emp no. field
		rs.updateString(2, tfEmp[1].getText()); // Name field
		rs.updateString(3, tfEmp[2].getText()); // Job
		rs.updateInt(4, Integer.parseInt(tfEmp[3].getText())); // manager #
		// rs.updateDate(5, java.sql.Date.valueOf(tfEmp[4].getText().trim())); // hire date
		rs.updateInt(6, Integer.parseInt(tfEmp[5].getText().trim())); // Salary
		rs.updateInt(7, Integer.parseInt(tfEmp[6].getText().trim())); // Comission
		rs.updateInt(8, Integer.parseInt(tfEmp[7].getText().trim())); // Dept. no. field
		rs.insertRow();
		cnn.commit();
		rs.close();
		rs = stmt.executeQuery("SELECT EmpNo, EName, Job, Mgr, HireDate, Sal, Comm, DeptNo FROM emp");
		rs.first();
		fillTextFields();
	   } catch ( SQLException e) { System.out.println(e.toString()) ; }
	}

	void deleteEmployee() {
		int ans ;
		ans = JOptionPane.showConfirmDialog(this, "Do you want to delete employee: " + tfEmp[1].getText() + " ?",
							  "Confirmation of Deletion", JOptionPane.YES_NO_OPTION);
		if ( ans == JOptionPane.YES_OPTION ) {
		    try {
			rs.deleteRow(); cnn.commit(); rs.first();
		    } catch (SQLException e) { System.out.println(e.toString()) ; }
		} else JOptionPane.showMessageDialog(this, "Deletion Cancelled.");
	}

	void updateEmployeeFields() {
	   try {
		rs.updateString(2, tfEmp[1].getText()); // Name field
		rs.updateString(3, tfEmp[2].getText()); // Job
		rs.updateInt(4, Integer.parseInt(tfEmp[3].getText())); // manager #
		// rs.updateDate(5, java.sql.Date.valueOf(tfEmp[4].getText().trim())); // hire date
		rs.updateInt(6, Integer.parseInt(tfEmp[5].getText())); // Salary
		rs.updateInt(7, Integer.parseInt(tfEmp[6].getText())); // Comission
		rs.updateRow();
		cnn.commit();
	   } catch (SQLException e) { System.out.println(e.toString()); }
	}

	void updateEmployeeField( int fdIndex ) {
		java.sql.Date hrDate = null;
		try {
		    switch ( fdIndex ) {
			case 1: case 2:	// Varchar2 field
				rs.updateString(fdIndex + 1, tfEmp[fdIndex].getText());
				rs.updateRow(); break;
			case 4:		// hire date.
				hrDate = java.sql.Date.valueOf(tfEmp[fdIndex].getText());
				rs.updateDate(fdIndex + 1, hrDate);
				rs.updateRow(); break;
			case 3: case 5: case 6:
				rs.updateInt(fdIndex + 1, Integer.parseInt(tfEmp[fdIndex].getText()));
				rs.updateRow(); break;
			default: JOptionPane.showMessageDialog(this, "The field is not updatable.");
				break;
		   }
		   cnn.commit();
		} catch (SQLException e) { System.out.println( e.toString()) ; System.exit(-1); };

	}

	void fillTextFields() {
		String s ;
		try {
			for ( int i = 0; i < 8 ; i ++ ) {
				s = rs.getString(i+1);
				if ( rs.wasNull() ) tfEmp[i].setText(""); else tfEmp[i].setText( s);
			}
		} catch (SQLException e) { e.printStackTrace(); }
	}

	public void onWindowClosing(WindowEvent e) {
		try {
			rs.close(); stmt.close(); cnn.close();
		} catch (SQLException e2) { e2.printStackTrace(); }
	}

	public static void main(String argv[] ) {
		new JDBC2UpdatableRS();
	}
}
			
